package cn.org.zx.common.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;
import java.util.Map;
import java.sql.PreparedStatement;

import java.math.BigDecimal;

import cn.org.zx.db.dbconnection.DBProperties;
import cn.org.zx.common.dispartPage.CreateDispartPage;
import cn.org.zx.common.dispartPage.CreatePageSQL;
import cn.org.zx.common.dispartPage.CreateResultCountSQL;
import cn.org.zx.db.dbconnection.DBFactory;
import cn.org.zx.db.dbexception.DBErrorException;

public abstract class DBAccess {
	/**
	 * 查询一页的结果集
	 * @param strSql
	 * @param strOrder 排序 
	 * @param iPageNum 每页条数
	 * @param iCurrentPage 当前是第几页
	 * @return
	 */
	public static List queryOnePage(String strSql,String strOrder,int iPageNum,int iCurrentPage){
		List listReturn = null;

		CreatePageSQL csql = null;		//创建分页sql对象
		csql = new CreatePageSQL(strSql,strOrder,iPageNum,iCurrentPage);
		//System.out.println(csql);
		listReturn = query(csql.toString());
		
		return listReturn;
	}
	
	/**
	 * 获取前台页面分页显示的html代码
	 * @param strSql
	 * @param iPageNum
	 * @param iCurrentPage
	 * @return
	 */
	public static String getDispartPageView(String strSql,int iPageNum,int iCurrentPage){
		
		int iTotleCount = 0;				//总数据条数
		int iPageTotal = 0;					//总页数

		CreateDispartPage cdp = null;		//创建分页视图代码对象
		String strPageView = "";			//分页试图代码		
		iTotleCount = getResultCount(strSql);
		iPageTotal = ((iTotleCount-1)/iPageNum)+1;
		cdp = new CreateDispartPage(iPageTotal,iCurrentPage,"doSearchByPage");
		strPageView = cdp.toString(); 
		return strPageView;
	}
	
	/**
	 * 查询结果集的所有行
	 * @param strSql
	 * @return 所有结果集的List集合,该集合中每一行封装为一个Map对象
	 */
	public static List query(String strSql){
		
		List listReturn = null;
    	Connection conn = null;
    	PreparedStatement pst = null;
    	ResultSet rs = null;

		try {
			conn = DBFactory.getConnection();
			pst = conn.prepareStatement(strSql);
			rs = pst.executeQuery();
    		listReturn = ResultSetUtils.ResultSetToList(rs);
		} catch (DBErrorException e) {
			e.printStackTrace();
		}catch (SQLException e) {
			e.printStackTrace();
		}finally{
			close(conn, pst, rs);
		} 		
		
		return listReturn;
	}
	
	/**
	 * 查询一行
	 * @param strSql
	 * @return 结果集的第一行的Map集合,如果结果集为空则返回一个空Map
	 */
	public static Map queryOneRow(String strSql){
		Map mapReturn = null;
    	Connection conn = null;
    	Statement stmt = null;
    	ResultSet rs = null;
		try {
			conn = DBFactory.getConnection();
			stmt = conn.createStatement();
			rs = stmt.executeQuery(strSql);
    		if(rs.next())
    			mapReturn = ResultSetUtils.ResultSetToMap(rs);
		} catch (DBErrorException e) {
			e.printStackTrace();
		}catch (SQLException e) {
			e.printStackTrace();
		}finally{
			close(conn, stmt, rs);
		} 		
		
		return mapReturn;
	}
	
	
	/**
	 * 获取结果集行数

	 * @param strSql
	 * @return
	 */
	public static int getResultCount(String strSql){
		int count = 0;
		Map mapCount = null;
		CreateResultCountSQL crcs = null;		//创建查询结果集总条数的sql
		String strTotleSql = "";				//分页试图代码
		
		crcs = new CreateResultCountSQL(strSql);
		strTotleSql = crcs.toString();
	
		mapCount = queryOneRow(strTotleSql);
		String strDBType = DBProperties.getDBType();
		if(strDBType.equals("sqlserver")){
			count = ((Integer)mapCount.get("counts")).intValue();
		}
		else if(strDBType.equals("oracle")){
			count = ((BigDecimal)mapCount.get("counts")).intValue();
		}
		
		
		return count;
	}
	
	
	
	
	/**
	 * 更新数据库,包括插入\修改\删除
	 * @param strSql
	 * @return
	 */
	public static boolean update(String strSql){
		boolean bReturn = true;
    	Connection conn = null;
    	Statement stmt = null;
		try {
			conn = DBFactory.getConnection();
			stmt = conn.createStatement();
			stmt.executeUpdate(strSql);
		} catch (DBErrorException e) {
			e.printStackTrace();
			bReturn = false;
		}catch (SQLException e) {
			e.printStackTrace();
			bReturn = false;
		}finally{
			close(conn, stmt);
		} 		
		
		return bReturn;
	}
	
	/**
	 * jdbc事务执行一组更新数据库操作,包括插入\修改\删除
	 * @param listSql
	 * @return
	 */
	public static boolean update(List listSql){
		boolean bReturn = true;
    	Connection conn = null;
    	Statement stmt = null;
    	String strSql = "";
		try {
			conn = DBFactory.getConnection();
			conn.setAutoCommit(false);
			stmt = conn.createStatement();
			for(Object obj:listSql){
				strSql = (String)obj;
				stmt.addBatch(strSql);
			}
			stmt.executeBatch();
			conn.commit();
		} catch (DBErrorException e) {
			e.printStackTrace();
			bReturn = false;
		}catch (SQLException e) {
			e.printStackTrace();
			bReturn = false;
		}finally{
			close(conn, stmt);
		} 		
		
		return bReturn;
	}
		
	/**
	 * 关闭资源
	 * @param conn
	 * @param stmt
	 * @param rs
	 */
	private static void close(Connection conn,Statement stmt,ResultSet rs){
		try {
			if(rs != null)
				rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
			rs = null;
		}
		close(conn, stmt);
	}
	
	/**
	 * 关闭资源
	 * @param conn
	 * @param stmt
	 */
	private static void close(Connection conn,Statement stmt){
		try {
			if(stmt != null)
				stmt.close();
		} catch (SQLException e) {
			e.printStackTrace();
			stmt = null;
		}
		close(conn);
	}
	
	/**
	 * 关闭资源
	 * @param conn
	 */
	private static void close(Connection conn){
		try {
			if(conn != null)
				conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
			conn = null;
		}
	}
		
	/**
	 * @param args
	 */
	public static void main(String[] args) {
		
	}

}
